package com.yangdm.util;

import com.yangdm.entity.TableFieldsAndTypesEntity;
import com.yangdm.entity.TableForeignKeyEntity;
import com.yangdm.entity.TableIndexEntity;
import com.yangdm.entity.TablePrimaryKeyEntity;
import io.micrometer.common.util.StringUtils;

import java.util.List;

/**
 * @program: oracle2mysql_tool
 * @description: SQL处理工具类
 * @author: yangdm
 * @create: 2024-12-24 16:09
 **/
public class SQLScriptGenerator {

    // 默认字符集
    private static final String DEFAULT_TABLE_CHARSET = "utf8mb4";

    // 默认排序规则
    private static final String DEFAULT_TABLE_COLLATION = "utf8mb4_general_ci";

    /**
     * 拼接删除及建表的SQL语句
     * @param tableName 表面
     * @param fieldsAndTypesList    字段及类型列表
     * @param primaryKeyList    主键列表
     * @param indexList 索引列表
     * @param tableCharset  字符集
     * @param tableCollation    排序规则
     * @return 字符串
     */
    public static String generateDropAndCreateTableSQLScripts(String tableName, List<TableFieldsAndTypesEntity> fieldsAndTypesList, List<TablePrimaryKeyEntity> primaryKeyList, List<TableIndexEntity> indexList, String tableCharset, String tableCollation) {
        StringBuilder sb = new StringBuilder();
        appendTableHeader(sb, tableName);
        appendDropAndCreateTable(sb, tableName);
        appendFieldsAndTypes(sb, fieldsAndTypesList);
        appendPrimaryKeys(sb, primaryKeyList);
        appendIndexes(sb, indexList);
        return formatTableStructure(sb, tableCharset, tableCollation);
    }

    /**
     * 追加描述及分隔符
     * @param sb
     * @param tableName
     */
    private static void appendTableHeader(StringBuilder sb, String tableName) {
        sb.append("-- ----------------------------\r\n")
                .append("-- Table structure for `").append(tableName).append("`;\r\n")
                .append("-- ----------------------------\r\n");
    }

    /**
     * 添加表删除及开始建表语句
     * @param sb
     * @param tableName
     */
    private static void appendDropAndCreateTable(StringBuilder sb, String tableName) {
        sb.append("DROP TABLE IF EXISTS `").append(tableName).append("`;\r\n")
                .append("CREATE TABLE `").append(tableName).append("` (\r\n");
    }

    /**
     * 追加字段及类型
     * @param sb
     * @param fieldsAndTypesList
     */
    private static void appendFieldsAndTypes(StringBuilder sb, List<TableFieldsAndTypesEntity> fieldsAndTypesList) {
        if (fieldsAndTypesList != null && !fieldsAndTypesList.isEmpty()) {
            for (TableFieldsAndTypesEntity field : fieldsAndTypesList) {
                sb.append("  `").append(field.getColumnName()).append("` ")
                        .append(field.getMysql2DataType())
                        .append(field.getNullFlag() == null ? "" : field.getNullFlag())
                        .append(StringUtils.isNotBlank(field.getDataDefault()) ? " DEFAULT " + field.getDataDefault() : "")
                        .append(",\r\n");
            }
        }
    }

    /**
     * 追加主键
     * @param sb
     * @param primaryKeyList
     */
    private static void appendPrimaryKeys(StringBuilder sb, List<TablePrimaryKeyEntity> primaryKeyList) {
        if (primaryKeyList != null && !primaryKeyList.isEmpty()) {
            for (TablePrimaryKeyEntity primaryKey : primaryKeyList) {
                sb.append("  PRIMARY KEY (").append(primaryKey.getpKeys()).append("),\r\n");
            }
        }
    }

    /**
     * 追加索引
     * @param sb
     * @param indexList
     */
    private static void appendIndexes(StringBuilder sb, List<TableIndexEntity> indexList) {
        if (indexList != null && !indexList.isEmpty()) {
            for (TableIndexEntity index : indexList) {
                String uniqueness = index.getUniqueness();
                // 是否为唯一索引
                sb.append("  ").append("UNIQUE".equals(uniqueness) ? "UNIQUE " : "").append(index.getIndexName()).append(" (").append(index.getColumnNames()).append("),\r\n");
            }
        }
    }

    /**
     * 添加表结束语句
     * @param sb
     * @param tableCharset
     * @param tableCollation
     * @return
     */
    private static String formatTableStructure(StringBuilder sb, String tableCharset, String tableCollation) {
        // 移除最后一个逗号，并添加表结束语句
        String tmpStr = sb.toString();
        int lastIndexCommaPos = tmpStr.lastIndexOf(",");
        if (lastIndexCommaPos > 0) {
            tmpStr = tmpStr.substring(0, lastIndexCommaPos) + tmpStr.substring(lastIndexCommaPos + 1).trim();
        }
        tmpStr += "\r\n) ENGINE=InnoDB DEFAULT CHARSET=" + (StringUtils.isNotBlank(tableCharset) ? tableCharset : DEFAULT_TABLE_CHARSET)
                + " COLLATE=" + (StringUtils.isNotBlank(tableCollation) ? tableCollation : DEFAULT_TABLE_COLLATION) + ";\r\n\r\n";
        return tmpStr;
    }


    /**
     * 拼接外键SQL脚本
     * @param tableName 表面
     * @param foreignKeyList    外键列表
     * @return  字符串
     */
    public static String generateForeignKeySQLScripts(String tableName, List<TableForeignKeyEntity> foreignKeyList) {
        StringBuilder sb = new StringBuilder();
        sb.append("-- ----------------------------\r\n")
                .append("-- GENERATE `").append(tableName).append("` TABLE FOREIGN KEY SCRIPT\r\n")
                .append("-- ----------------------------\r\n");
        if (foreignKeyList != null && !foreignKeyList.isEmpty()) {
            for (TableForeignKeyEntity foreignKey: foreignKeyList) {
                String foreignKeySqlScript = foreignKey.getForeignKeyScript();
                sb.append(foreignKeySqlScript)
                        .append("\r\n");
            }
        }
        return sb.toString();
    }

}
